package com.tongji.run;

import com.ibeetl.admin.core.util.DateUtil;
import com.tongji.run.manager.ManagerFactory;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.util.*;

public class 智能客服回访名单 {



    public static String root = "E:\\文档\\2024\\回访记录\\智能客服回访\\";

    public static void main(String[] args) {
        List list = new ArrayList();

        list.addAll(create19tianList());
        list.addAll(create30tianList());
        list.addAll(create60tianList());
        list.addAll(create90tianList());
        list.addAll(create180tianList());
        list.addAll(create365tianList());


        HashMap data = new HashMap();
        data.put("donorList",list);
        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//总(" + list.size() + ")-" + ymd + ".xlsx",data);

        create14tianCannotRegisterList();
    }

    public static List<HashMap> create90tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        
        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -91 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -90 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//90天（" + list.size() + "）.xlsx",data);
        
        return list;
    }
    public static List<HashMap> create60tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -61 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -60 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//60天（" + list.size() + "）.xlsx",data);
        return list;
    }

    public static List<HashMap> create180tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期,donor.remark\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -181 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -180 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//180天（" + list.size() + "）.xlsx",data);
        return list;
    }
    public static List<HashMap> create365tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select donor.donorId as dId,donor.donorName, donor.donorNo,donor.phone,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d') as 最后采浆日期,donor.remark\n" +
                " from t_bims_donor as donor where donor.plasmaDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -366 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -365 DAY),'%Y%m%d2359000') and donor.codeDonorState = 'N'\n" +
                ") as a where a.是否在黑名单中 = 0 ",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//365天（" + list.size() + "）.xlsx",data);
        return list;
    }
    public static List<HashMap> create19tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 14 DAY)) as 14天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -19 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -19 DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数<24",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//19天（" + list.size() + "）.xlsx",data);
        return list;
    }
    public static List<HashMap> create30tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 14 DAY)) as 14天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -31 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -30 DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数<24",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//30天（" + list.size() + "）.xlsx",data);
        return list;
    }

    public static List<HashMap> create13tianList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 13 DAY)) as 13天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数<24",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//13天（" + list.size() + "）.xlsx",data);
        return list;
    }

    public static List<HashMap> create14tianCannotRegisterList() {
        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        HashMap data = new HashMap();
        List<HashMap> list = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                "select collection.donorId as dId,donor.donorName, donor.donorNo,donor.phone,\n" +
                "(select count(*) from t_bims_black_list as back_list where back_list.donorId = dId) > 0 as 是否在黑名单中,\n" +
                "(select count(*) from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') and c.weight > 0 ) as 次数,\n" +
                "(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d')) as 365天内首次献浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE((select c.beginDate from t_bims_plasma_collection as c where c.donorId = dId and c.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -364 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY),'%Y%m%d2359000') order by c.beginDate asc limit 0,1 ),'%Y%m%d'), INTERVAL 365 DAY)) as 下次可献浆日期,\n" +
                "DATE_FORMAT(collection.beginDate,'%Y-%m-%d') as 本次采浆日期,\n" +
                "DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期,\n" +
                "(DATE_ADD(STR_TO_DATE(collection.beginDate,'%Y%m%d'), INTERVAL 14 DAY)) as 14天到期日期\n" +
                " from t_bims_plasma_collection collection left join t_bims_donor as donor on collection.donorId = donor.donorId where \n" +
                " collection.beginDate between DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d0000000') and DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL -13 DAY),'%Y%m%d2359000') and \n" +
                " collection.weight > 0 and donor.codeDonorState = 'N'\n" +
                ") as a where a.最后采浆日期 = a.本次采浆日期 and a.是否在黑名单中 = 0 and a.次数=24 and a.下次可献浆日期 > a.14天到期日期",
                new Object[]{}), HashMap.class);
        data.put("donorList",list);

        if (list.size() == 0) return list;

        String ymd = DateUtil.format(new Date(), "yyyy年M月d日");
        File rootFile = new File(root + "//" + ymd);
        rootFile.mkdirs();
        ManagerFactory.createExcel(root,
                "excelTemplates/智能客服回访/回访模版.xlsx",
                ymd + "//14天24次通知（" + list.size() + "）.xlsx",data);

        return list;
    }
}
